import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
import numpy as np
from statsmodels.tsa.arima.model import ARIMA
from statsmodels.tsa.arima_model import ARMA
import warnings
from statsmodels.tsa.api import ExponentialSmoothing, SimpleExpSmoothing, Holt, SARIMAX
from sklearn.metrics import mean_squared_error
import os
import sklearn
import math

# Current directory of File
directory = os.getcwd()

df_PL = pd.read_excel(r'F:\P&L_forecasting\P_L_Historical_qtr.xlsx', sheet_name='FY17_onwrds') df_BS = pd.read_excel(r'F:\P&L_forecasting\BS_qtr.xlsx', sheet_name='FY17_onwrds') df12 = pd.read_excel(r'F:\P&L_forecasting\P_L_Historical_qtr.xlsx', sheet_name='trnsps_num') df_macro = pd.read_excel(r'F:\P&L_forecasting\Macro_data.xlsx', sheet_name='data')

df_PL = pd.read_excel(str(directory + '\P_L_Historical_qtr.xlsx'), sheet_name='FY17_onwrds')
df_BS = pd.read_excel(str(directory + '\BS_qtr.xlsx'), sheet_name='FY17_onwrds')
#df12 = pd.read_excel(r'F:\P&L_forecasting\P_L_Historical_qtr.xlsx', sheet_name='trnsps_num')
df_macro = pd.read_excel(str(directory + '\Macro_data.xlsx'), sheet_name='data')

#Defining MAPE function
def MAPE(Y_actual,Y_Predicted):
    mape = np.mean(np.abs((Y_actual - Y_Predicted)/Y_actual))*100
    return mape

df1=df.set_index('Date') # Compute the correlation matrix corr = df1.corr() # Generate a mask for the upper triangle mask = np.triu(np.ones_like(corr, dtype=bool)) # Set up the matplotlib figure f, ax = plt.subplots(figsize=(11, 9)) # Generate a custom diverging colormap cmap = sns.diverging_palette(230, 20, as_cmap=True) # Draw the heatmap with the mask and correct aspect ratio sns.heatmap(corr, mask=mask, cmap=cmap, vmax=.3, center=0, square=True, linewidths=.5, cbar_kws={"shrink": .5}) df_macro['Gross NPAs'].corr(df_PL['Interest on Advances'])#--------------Check_stationarity------------- #Ad-fuller_test #define function for ADF test from statsmodels.tsa.stattools import adfuller def adf_test(timeseries): #Perform Dickey-Fuller test: print ('Results of Dickey-Fuller Test:') dftest = adfuller(timeseries, autolag='AIC') dfoutput = pd.Series(dftest[0:4], index=['Test Statistic','p-value','#Lags Used','Number of Observations Used']) for key,value in dftest[4].items(): dfoutput['Critical Value (%s)'%key] = value print (dfoutput) adf_test(df1['Interest on Advances'])# plot all finance metrics for i in to_plot: plt.plot(df1['Quarters'],df1[i]) plt.xticks(rotation=60) plt.title(i + ' plot') plt.show()# Differencing import numpy as np, pandas as pd from statsmodels.graphics.tsaplots import plot_acf, plot_pacf import matplotlib.pyplot as plt plt.rcParams.update({'figure.figsize':(9,7), 'figure.dpi':120}) # Original Series fig, axes = plt.subplots(3, 2, sharex=True) axes[0, 0].plot(df['Interest on Advances']); axes[0, 0].set_title('Original Series') plot_acf(df['Interest on Advances'], ax=axes[0, 1]) # 1st Differencing axes[1, 0].plot(df['Interest on Advances'].diff()); axes[1, 0].set_title('1st Order Differencing') plot_acf(df['Interest on Advances'].diff().dropna(), ax=axes[1, 1]) # 2nd Differencing axes[2, 0].plot(df['Interest on Advances'].diff().diff()); axes[2, 0].set_title('2nd Order Differencing') plot_acf(df['Interest on Advances'].diff().diff().dropna(), ax=axes[2, 1]) plt.show()

def Chart_plot(col_name, fc_series):
    plt.figure(figsize=(12,8), dpi=200)
    plt.plot(train, label='training',marker = 'o', linestyle= '--')
    plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
    plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'orange')

    mon = ['Jun', "Sep", 'Dec', 'Mar']
    #Q= ['Q1','Q2','Q3','Q4']
    yrs = list(np.arange(17,23,1))
    x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

    x = np.arange(0, len(train) + len(fc_series), 1)
    plt.xticks(x, x_tks, rotation=75)
    plt.legend(loc='upper left', fontsize=8)
    plt.grid(True)

    for x,y in zip(x,df_PL[col_name]):
        label = "{:.2f}".format(y)
        plt.annotate(label, # this is the text
                     (x,y), # these are the coordinates to position the label
                     textcoords="offset points", # how to position the text
                     xytext=(0,10), # distance from text to points (x,y)
                     color = 'b',
                     ha='center') # horizontal alignment can be left, right or center

    tst_x = np.arange(18,23, 1)
    for x,y in zip(tst_x,fc_series):
        label = "{:.2f}".format(y)
        plt.annotate(label, # this is the text
                     (x,y), # these are the coordinates to position the label
                     textcoords="offset points", # how to position the text
                     xytext=(0,10), # distance from text to points (x,y)
                     color = 'darkorange',
                     ha='right') # horizontal alignment can be left, right or center

    print(fc_series)
    mape = "{:.3f}".format(MAPE(test, fc_series))
    #print('MAPE :: %s' %mape, '%')
    plt.title(col_name +'- Forecating'+'----MAPE--'+str(mape)+'%')
    #plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))

Predictors = ['Yield on Advances' ,'Gross Advances']
Target =  'Interest on Advances'
# Define Total Count of Data
Tot_cnt = 20

# Define No. of Samples to be used for Training
Tr_cnt = 16

df_mac = df_macro[Predictors]
Forecast_test = pd.DataFrame()
Forecast_test[Predictors[0]]= Macro_forecast_arima(Predictors[0],Tr_cnt,Tot_cnt - Tr_cnt)
Forecast_test[Predictors[1]]= Macro_forecast_arima(Predictors[1],Tr_cnt,Tot_cnt - Tr_cnt)
Forecast_test

Best ARIMA(0, 0, 1) MAPE=4.440
16    8.534148
17    8.771056
18    8.771056
19    8.771056
Name: mean, dtype: float64
Best ARIMA(2, 1, 1) MAPE=2.272
16    2.401808e+06
17    2.420330e+06
18    2.402889e+06
19    2.419329e+06
Name: mean, dtype: float64

	Yield on Advances	Gross Advances
16	8.534148	2.401808e+06
17	8.771056	2.420330e+06
18	8.771056	2.402889e+06
19	8.771056	2.419329e+06

col_name = 'Gross Advances'
tr_cnt = 16
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.8)
fc_series = exp_model.forecast(4)

# Holt Winters
#fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.25, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)

# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')

mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)

x_cor = len(train) - 8
y_cor = max(train)- max(train) * 0.005
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):
    label = round(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
print(fc_series)
mape = "{:.3f}".format(MAPE(test, fc_series))
print('MAPE :: %s' %mape, '%')
plt.title(col_name +'- Forecating'+'----MAPE--'+str(mape)+'%')
plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))
GA = fc_series
GA

C:\Users\1015136\AppData\Roaming\Python\Python38\site-packages\statsmodels\tsa\holtwinters\model.py:915: ConvergenceWarning: Optimization failed to converge. Check mle_retvals.
  warnings.warn(

16    2.363202e+06
17    2.393161e+06
18    2.446524e+06
19    2.563679e+06
dtype: float64
MAPE :: 0.677 %

16    2.363202e+06
17    2.393161e+06
18    2.446524e+06
19    2.563679e+06
dtype: float64

col_name = 'Yield on Advances'
tr_cnt = 16
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]

# Exponential Smoothing
#exp = ExponentialSmoothing(train, trend='ADD', seasonal='ADD', seasonal_periods=4)
#exp_model = exp.fit(smoothing_level=0.8)
#fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=False, initialization_method="heuristic").fit(smoothing_level=0.25, smoothing_trend=0.1,optimized=False)
fc_series = fit2.forecast(4)

# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper right', fontsize=8)
plt.grid(True)

x_cor = len(train) - 8
y_cor = max(train)- max(train) * 0.05
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):

    label = "{:.2f}".format(y)

    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))
print('MAPE :: %s' %"{:.3f}".format(MAPE(test, fc_series)), '%')
YOA = fc_series
YOA

MAPE :: 0.678 %

16    8.291543
17    8.230073
18    8.168604
19    8.107135
dtype: float64

# For forecast interval col_name = 'Gross Advances' tr_cnt = 16 train = df_macro[col_name][:tr_cnt] test = df_macro[col_name][tr_cnt:] # Other Forecasting Models # Exponential Smoothing #exp = ExponentialSmoothing(train, seasonal='mul', seasonal_periods=4) exp = ExponentialSmoothing(train, trend='add', seasonal='mul', seasonal_periods=4) exp_model = exp.fit(smoothing_level=0.8) simulations = exp_model.simulate(8, repetitions = 500, error = 'add') #print(simulations) ax = train.plot(figsize = (12,8), marker = 'o', color = 'black') exp_model.fittedvalues.plot(ax=ax, style = '-', color = 'green') simulations.plot(ax =ax, style= '-', alpha = 0.05, color = 'grey', legend = False)

col_name  = 'Gross NPAs'
train = df_macro[col_name][:18]
test = df_macro[col_name][18:]
# evaluate parameters
p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
ordr = evaluate_models(df_macro[col_name], p_values, d_values, q_values)

model = ARIMA(train, order=ordr)
model_fit = model.fit()

result = model_fit.get_forecast(steps=2)
output = result.summary_frame(alpha = 0.05)
print(output['mean'])
fc_series = pd.Series(output['mean'])
lower_series = pd.Series(output['mean_ci_lower'])
upper_series = pd.Series(output['mean_ci_upper'])
# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.fill_between(lower_series.index, lower_series, upper_series, 
                 color='k', alpha=.15)
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)

x_cor = len(train) - 5
y_cor = max(train)- max(train) * 0.05
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):

    label = "{:.2f}".format(y)

    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
plt.savefig(str(directory +'\\'+ col_name + '_2.jpg'))
#G_NPA = fc_series

Best ARIMA(1, 1, 1) MAPE=3.803
18    123260.563331
19    121508.912846
Name: mean, dtype: float64

# grid search ARIMA parameters for time series
# evaluate an ARIMA model for a given order (p,d,q)
def evaluate_sarimax_model(X, arima_order, seasonal_order,train_size,frcst_stp):
    # prepare training dataset
    #train_size = 16 #int(len(X) * 0.66)
    train, test = X[0:train_size], X[train_size:]
    history = [x for x in train]
    # make predictions
    predictions = list()
    for t in range(len(test)):
        model = SARIMAX(history, order=arima_order, seasonal_order=seasonal_order)
        model_fit = model.fit()
        yhat = model_fit.forecast(steps = frcst_stp)[0]
        predictions.append(yhat)
    # calculate out of sample error
    mape_ = MAPE(test, predictions)
    return mape_

# evaluate combinations of p, d and q values for an ARIMA model
def evaluate_ssnl_models(dataset, p_values, d_values, q_values, P_values, D_values, Q_values,tr_cnt,frcst_stp):
    dataset = dataset.astype('float32')
    best_score, best_cfg = float("inf"), None
    for p in p_values:
        for d in d_values:
            for q in q_values:
                for P in P_values:
                    for D in D_values:
                        for Q in Q_values:
                            #print(p,d,q)
                            order = (p,d,q)
                            seasonal_order = (P,D,Q,4)
                            try:
                                mape = evaluate_sarimax_model(dataset, order, seasonal_order,tr_cnt,frcst_stp)
                                if mape < best_score:
                                    best_score, best_cfg, best_s_cfg = mape, order, seasonal_order
                                #print('SARIMAX%s MAPE=%.3f' % (order,mape))
                            except:
                                continue
    print('Best ARIMA%s Seasonal%s MAPE=%.3f' % (best_cfg, best_s_cfg, best_score))
    return best_cfg, best_s_cfg
 

def Macro_forecast_sarimax(col_name,tr_cnt,frcst_cnt):
    # evaluate parameters
    p_values = [0, 1]
    d_values = [0,1]
    q_values = [0,1]
    P_values = [0, 1]
    D_values = [0, 1]
    Q_values = [0, 1]
    warnings.filterwarnings("ignore")
    ordr, s_ordr = evaluate_ssnl_models(df_macro[col_name], p_values, d_values, q_values, P_values, D_values, Q_values, tr_cnt)
    train = df_macro[col_name][:tr_cnt]
    test = df_macro[col_name][tr_cnt:]
    best_order = ordr
    best_seasonal_order = s_ordr
    # define model
    model = SARIMAX(train, order=best_order, seasonal_order=best_seasonal_order)
    model_fit = model.fit()

    result = model_fit.get_forecast(steps=frcst_cnt)
    output = result.summary_frame(alpha = 0.05)
    print(output['mean'])
    fc_series = pd.Series(output['mean'])
    lower_series = pd.Series(output['mean_ci_lower'])
    upper_series = pd.Series(output['mean_ci_upper'])
    # Plot
    plt.figure(figsize=(12,8), dpi=200)
    plt.plot(train, label='training',marker = 'o', linestyle= '--')
    plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
    plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
    plt.fill_between(lower_series.index, lower_series, upper_series, 
                     color='k', alpha=.15)
    plt.title(col_name +'- Forecating')
    mon = ['Jun', "Sep", 'Dec', 'Mar']
    #Q= ['Q1','Q2','Q3','Q4']
    yrs = list(np.arange(17,23,1))
    x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

    x = np.arange(0, len(train) + len(fc_series), 1)
    plt.xticks(x, x_tks, rotation=75)
    plt.legend(loc='upper left', fontsize=8)
    plt.grid(True)
    x_cor = len(train) - 5
    y_cor = max(train)- max(train) * 0.05
    g = pd.DataFrame()
    g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
    g['Value'] = pd.Series(fc_series.reset_index(drop = True))
    g = g.round(2)
    plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))
    for x,y in zip(x,df_macro[col_name]):

        label = "{:.2f}".format(y)

        plt.annotate(label, # this is the text
                     (x,y), # these are the coordinates to position the label
                     textcoords="offset points", # how to position the text
                     xytext=(0,10), # distance from text to points (x,y)
                     ha='center') # horizontal alignment can be left, right or center
    #plt.savefig('C:/Users/1015136/Downloads/P&L_forecasting/P&L_forecasting/Horizon 2 Images/'+col_name + '.jpg')
    plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name +'_ssnl'+ '.jpg'))
    return fc_series

# grid search ARIMA parameters for time series
# evaluate an ARIMA model for a given order (p,d,q)
def evaluate_arima_model(X, arima_order,train_size,frcst_stp):
    # prepare training dataset
    #train_size = 18 #int(len(X) * 0.66)
    train, test = X[0:train_size], X[train_size:]
    history = [x for x in train]
    # make predictions
    predictions = list()
    for t in range(len(test)):
        #print(t)
        model = ARIMA(history, order=arima_order)
        model_fit = model.fit()
        yhat = model_fit.forecast(steps = frcst_stp)[0]
        predictions.append(yhat)
    # calculate out of sample error
    mape_ = MAPE(test, predictions)
    return mape_

# evaluate combinations of p, d and q values for an ARIMA model
def evaluate_models(dataset, p_values, d_values, q_values,train_size,frcst_stp):
	dataset = dataset.astype('float32')
	best_score, best_cfg = float("inf"), None
	for p in p_values:
		for d in d_values:
			for q in q_values:
				order = (p,d,q)
				try:
					mape = evaluate_arima_model(dataset, order,train_size,frcst_stp)
					if mape < best_score:
						best_score, best_cfg = mape, order
					#print('ARIMA%s MAPE=%.3f' % (order,mape))
				except:
					continue
	print('Best ARIMA%s MAPE=%.3f' % (best_cfg, best_score))
	return best_cfg

def Macro_forecast_arima(col_name,tr_cnt,frcst_cnt):
    # evaluate parameters
    p_values = [0, 1, 2]
    d_values = range(0,2)
    q_values = range(0, 2)
    warnings.filterwarnings("ignore")
    best_order = evaluate_models(df_macro[col_name], p_values, d_values, q_values,tr_cnt,frcst_cnt)
    train = df_macro[col_name][:tr_cnt]
    test = df_macro[col_name][tr_cnt:]
    # define model
    model = ARIMA(train, order=best_order)
    model_fit = model.fit()

    result = model_fit.get_forecast(steps=frcst_cnt)
    output = result.summary_frame(alpha = 0.05)
    #print(output['mean'])
    fc_series = pd.Series(output['mean'])
    lower_series = pd.Series(output['mean_ci_lower'])
    upper_series = pd.Series(output['mean_ci_upper'])
    # Plot
    plt.figure(figsize=(12,8), dpi=200)
    plt.plot(train, label='training',marker = 'o', linestyle= '--')
    plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
    plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
    plt.fill_between(lower_series.index, lower_series, upper_series, 
                     color='k', alpha=.15)
    plt.title(col_name +'- Forecating')
    mon = ['Jun', "Sep", 'Dec', 'Mar']
    #Q= ['Q1','Q2','Q3','Q4']
    yrs = list(np.arange(17,23,1))
    x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

    x = np.arange(0, len(train) + len(fc_series), 1)
    plt.xticks(x, x_tks, rotation=75)
    plt.legend(loc='upper left', fontsize=8)
    plt.grid(True)
    x_cor = len(train) - 5
    y_cor = max(train)- max(train) * 0.05
    g = pd.DataFrame()
    g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
    g['Value'] = pd.Series(fc_series.reset_index(drop = True))
    g = g.round(2)
    plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))
    for x,y in zip(x,df_macro[col_name]):

        label = "{:.2f}".format(y)

        plt.annotate(label, # this is the text
                     (x,y), # these are the coordinates to position the label
                     textcoords="offset points", # how to position the text
                     xytext=(0,10), # distance from text to points (x,y)
                     ha='center') # horizontal alignment can be left, right or center
    #plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '_non_ssnl' + '.jpg'))
    return fc_series

def ML_REG_ALGOS(reg_train_x, reg_train_y, reg_test_x, reg_test_y):

    results = pd.DataFrame()
    rmse = ['RMSE']
    results['Actual'] = reg_test_y
    from sklearn.linear_model import LinearRegression

    reg = LinearRegression().fit(reg_train_x, reg_train_y)
    pred = reg.predict(reg_test_x)
    results['LR o/p'] = pred
    rmse.append(mean_squared_error(reg_test_y, pred,squared=False))
    #mse = sklearn.metrics.mean_squared_error(reg_test_y, pred)
    #rmse = math.sqrt(mse)
    #print(rmse)

    '''
    # Feature Importance
    imp = reg.coef_
    f_imp = pd.DataFrame({'feature': list(reg_train_x), 'Feature_imp' : [abs(v) for i,v in enumerate(imp)]})
    print(f_imp)
    '''
    from sklearn import tree

    reg = tree.DecisionTreeRegressor()
    reg.fit(reg_train_x, reg_train_y)
    pred = reg.predict(reg_test_x)
    results['DT o/p'] = pred
    rmse.append(mean_squared_error(reg_test_y, pred,squared=False))

    from sklearn.ensemble import RandomForestRegressor

    reg = RandomForestRegressor()
    reg.fit(reg_train_x, reg_train_y)
    pred = reg.predict(reg_test_x)
    results['RF o/p'] = pred
    rmse.append(mean_squared_error(reg_test_y, pred,squared=False))

    from xgboost import XGBRegressor

    reg = XGBRegressor(eta= 0.1, tree_method = 'approx')
    reg.fit(reg_train_x, reg_train_y)
    pred = reg.predict(reg_test_x)
    results['XGB o/p'] = pred
    rmse.append(mean_squared_error(reg_test_y, pred,squared=False))

    from sklearn.svm import SVR

    reg = SVR()
    reg.fit(reg_train_x, reg_train_y)
    pred = reg.predict(reg_test_x)
    results['SVM o/p'] = pred
    rmse.append(mean_squared_error(reg_test_y, pred,squared=False))
    
    results.loc[len(results.index)] = rmse
    print(results)

Predictors = ['Yield on Advances' ,'Gross Advances']
Target =  'Interest on Advances'

#Predictors = ['Cost of Deposits ' ,'Deposits ']
#Target =  'Interest on Deposits'

#Predictors = ['Yield on Investments' ,'Investments']
#Target =  'Int on Resources Operations'

# Define Total Count of Data
Tot_cnt = 20

# Define No. of Samples to be used for Training
Tr_cnt = 16

df_mac = df_macro[Predictors]
Forecast_test = pd.DataFrame()
Forecast_test[Predictors[0]]= Macro_forecast_sarimax(Predictors[0],Tr_cnt,Tot_cnt - Tr_cnt)
Forecast_test[Predictors[1]]= Macro_forecast_sarimax(Predictors[1],Tr_cnt,Tot_cnt - Tr_cnt)
Forecast_test

Best ARIMA(0, 0, 1) Seasonal(1, 0, 1, 4) MAPE=3.748
16    8.247111
17    8.613269
18    8.886624
19    8.527470
Name: mean, dtype: float64
Best ARIMA(0, 1, 0) Seasonal(1, 0, 1, 4) MAPE=2.257
16    2.407124e+06
17    2.406055e+06
18    2.404352e+06
19    2.399907e+06
Name: mean, dtype: float64

	Yield on Advances	Gross Advances
16	8.247111	2.407124e+06
17	8.613269	2.406055e+06
18	8.886624	2.404352e+06
19	8.527470	2.399907e+06

df_macro['Gross NPAs'].head() #reg_train_x.head() training_cnt = 18 reg_train_x = df_macro[['Yield on Advances' ,'Gross Advances','Gross NPAs']][:training_cnt] Forecast_test['Gross NPAs'] = G_NPA #reg_test_x = Forecast_test reg_test_x = df_macro[['Yield on Advances' ,'Gross Advances','Gross NPAs']][training_cnt:] Target = 'Interest on Advances' reg_train_y = df_PL[:20][Target][:training_cnt] reg_test_y = df_PL[:20][Target][training_cnt:] Forecast_test

# Results from individual modeling

Predictors = ['Yield on Advances' ,'Gross Advances']
Target =  'Interest on Advances'

training_cnt = 16
Forecast_test = pd.DataFrame()

Forecast_test[Predictors[0]]= YOA
Forecast_test[Predictors[1]]= GA

reg_train_x = df_macro[Predictors][:training_cnt]
reg_test_x = df_macro[Predictors][training_cnt:]
reg_test_x = Forecast_test
#print(reg_test_x)
reg_train_y = df_PL[:20][Target][:training_cnt]
reg_test_y = df_PL[:20][Target][training_cnt:]
ML_REG_ALGOS(reg_train_x, reg_train_y, reg_test_x, reg_test_y)

        Actual        LR o/p        DT o/p        RF o/p       XGB o/p  \
16  44101.1059  43721.230772  42627.747995  42923.498951  40723.765625   
17  43377.5765  44059.123359  42627.747995  42923.498951  40723.765625   
18  43736.7442  44848.971972  42627.747995  42923.498951  40674.835938   
19  40213.7123  46870.760526  42627.747995  42923.498951  40674.835938   
4         RMSE   3397.136770   1564.486116   1548.971923   2647.501497   

         SVM o/p  
16  38960.567827  
17  38960.611819  
18  38960.687744  
19  38960.841175  
4    4192.705272  

# Results from individual modeling

Predictors = ['Yield on Advances' ,'Gross Advances']
Target =  'Interest on Advances'

from sklearn.preprocessing import MinMaxScaler
df_macro = pd.read_excel(str(directory + '\Macro_data.xlsx'), sheet_name='data')

training_cnt = 16
Forecast_test = pd.DataFrame()


Forecast_test[Predictors[0]]= YOA
Forecast_test[Predictors[1]]= GA/1e5

#df_macro['Gross Advances'] = df_macro['Gross Advances']/1e6
trans = MinMaxScaler()
df_macro[Predictors] = trans.fit_transform(df_macro[Predictors])
reg_train_x = df_macro[Predictors][:training_cnt]
reg_test_x = df_macro[Predictors][training_cnt:]
print(reg_train_x.head())
#reg_test_x = Forecast_test
print(reg_test_x)
reg_train_y = df_PL[:20][Target][:training_cnt]
reg_test_y = df_PL[:20][Target][training_cnt:]
#ML_REG_ALGOS(reg_train_x, reg_train_y, reg_test_x, reg_test_y)

from sklearn.linear_model import LinearRegression
from sklearn.ensemble import RandomForestRegressor

from xgboost import XGBRegressor
from sklearn.svm import SVR
reg = SVR()
#reg = XGBRegressor(eta= 0.1, tree_method = 'approx')
reg.fit(reg_train_x, reg_train_y)
#reg = LinearRegression().fit(reg_train_x, reg_train_y)
pred = reg.predict(reg_test_x)

SS_Residual = sum((reg_test_y-pred)**2)      

SS_Total = sum((reg_test_y-np.mean(reg_test_y))**2)    
r_squared = 1 - (SS_Residual)/SS_Total

adjusted_r_squared = 1 - (1-r_squared)*(len(reg_test_y)-1)/(len(reg_test_y)-reg_train_x.shape[1]-1)
print ('R-Squared %s' %r_squared, 'Adjusted R-Squared %s' %adjusted_r_squared)

   Yield on Advances  Gross Advances
0           1.000000        0.000000
1           0.896719        0.022360
2           0.875823        0.026131
3           0.821269        0.136781
4           0.316108        0.039938
    Yield on Advances  Gross Advances
16           0.233697        0.773851
17           0.156117        0.770887
18           0.116177        0.878234
19           0.000000        1.000000
R-Squared -6.329020660609952 Adjusted R-Squared -20.987061981829857

list(reg_train_x)

['Yield on Advances', 'Gross Advances', 'Gross NPAs']

# Tree Visualization reg = tree.DecisionTreeRegressor() reg.fit(reg_train_x, reg_train_y) pred = reg.predict(reg_test_x) print('DT o/p') print(pred) print('DT RMSE:') print(mean_squared_error(reg_test_y, pred,squared=False)) #tree.export_text(reg) _ = tree.plot_tree(reg, feature_names= list(reg_train_x), filled = True)

col_name = 'Interest on Advances'
tr_cnt = 16
train = df_PL[col_name][:20][:tr_cnt]
test = df_PL[col_name][:20][tr_cnt:]
# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='ADD')
exp_model = exp.fit(smoothing_level=0.8)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=False, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.05,optimized=False)
#fc_series = fit2.forecast(4)

# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper right', fontsize=8)
plt.grid(True)

x_cor = len(train) - 8
y_cor = max(train)- max(train) * 0.05
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_PL[col_name]):

    label = "{:.2f}".format(y)

    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
#plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))
print('MAPE :: %s' %MAPE(test, fc_series))


print('RMSE :: %s' %mean_squared_error(test, fc_series,squared=False))
YOA = fc_series
YOA

MAPE :: 3.1158772898642546
2005.0931309593616
RMSE :: 2005.0931309593616

C:\Users\1015136\AppData\Roaming\Python\Python38\site-packages\statsmodels\tsa\holtwinters\model.py:915: ConvergenceWarning: Optimization failed to converge. Check mle_retvals.
  warnings.warn(

16    43731.041568
17    43875.826718
18    44020.611868
19    44165.397018
dtype: float64

col_name = 'Cost of Deposits '
tr_cnt = 16
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.8)
#fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.01, smoothing_trend=0.05,optimized=False)
fc_series = fit2.forecast(4)

# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)

x_cor = len(train) - 7
y_cor = max(train)- max(train) * 0.1
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))
print('MAPE :: %s' %"{:.3f}".format(MAPE(test, fc_series)), '%')
COD = fc_series
COD

MAPE :: 0.663 %

16    4.474645
17    4.387971
18    4.302976
19    4.219627
dtype: float64

col_name = 'Deposits '
tr_cnt = 16
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='add', seasonal_periods=4)
#exp = ExponentialSmoothing(train, trend='mul')
exp_model = exp.fit(smoothing_level=0.9)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.5,optimized=False)
fc_series = fit2.forecast(4)

# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)

x_cor = len(train) - 2
y_cor = max(train)- max(train) * 0.2
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))

print('MAPE :: %s' %"{:.3f}".format(MAPE(test, fc_series)), '%')
DEPO = fc_series
DEPO

MAPE :: 2.219 %

C:\Users\1015136\AppData\Roaming\Python\Python38\site-packages\statsmodels\tsa\holtwinters\model.py:915: ConvergenceWarning: Optimization failed to converge. Check mle_retvals.
  warnings.warn(

16    3.299835e+06
17    3.397222e+06
18    3.497483e+06
19    3.600703e+06
dtype: float64

# Results from individual modeling

Predictors = ['Cost of Deposits ' ,'Deposits ']
Target =  'Interest on Deposits'

training_cnt = 16
Forecast_test = pd.DataFrame()

Forecast_test[Predictors[0]]= COD
Forecast_test[Predictors[1]]= DEPO

reg_train_x = df_macro[Predictors][:training_cnt]
reg_test_x = df_macro[Predictors][training_cnt:]
reg_test_x = Forecast_test
#print(reg_test_x)
reg_train_y = df_PL[:20][Target][:training_cnt]
reg_test_y = df_PL[:20][Target][training_cnt:]
ML_REG_ALGOS(reg_train_x, reg_train_y, reg_test_x, reg_test_y)

         Actual        LR o/p        DT o/p        RF o/p       XGB o/p  \
16   36839.3456  36825.297275  37168.108344  37128.897736  37147.039062   
17  35848.91837  37368.345655  37168.108344  37128.897736  37147.039062   
18  35121.15103  37941.475555  37168.108344  37128.897736  37147.039062   
19   34625.8321  38545.302520  37168.108344  37128.897736  37147.039062   
4          RMSE   2531.073657   1767.876543   1733.390610   1749.321259   

         SVM o/p  
16  35195.487357  
17  35195.580055  
18  35195.665880  
19  35195.743049  
4     929.982773  

col_name = 'Yield on Investments'
tr_cnt = 16
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods=4)
#exp = ExponentialSmoothing(train, trend='mul')
exp_model = exp.fit(smoothing_level=0.9)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.1,optimized=False)
fc_series = fit2.forecast(4)


'''
p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_macro[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])

'''






# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)

x_cor = len(train) - 7
y_cor = max(train)- max(train) * 0.1
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
#plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center

tst_x = np.arange(16,21, 1)
for x,y in zip(tst_x,fc_series):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 color = 'g',
                 ha='right') # horizontal alignment can be left, right or center
    
plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))
print('MAPE :: %s' %"{:.3f}".format(MAPE(test, fc_series)), '%')
YOI = fc_series
YOI

MAPE :: 6.940 %

16    6.956850
17    6.909482
18    6.862437
19    6.815712
dtype: float64

col_name = 'Investments'
tr_cnt = 16
train = df_macro[col_name][:tr_cnt]
test = df_macro[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods=4)
#exp = ExponentialSmoothing(train, trend='mul')
exp_model = exp.fit(smoothing_level=0.9)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)

# Plot
plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'limegreen')
plt.title(col_name +'- Forecating')
mon = ['Jun', "Sep", 'Dec', 'Mar']
#Q= ['Q1','Q2','Q3','Q4']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)

x_cor = len(train) - 7
y_cor = max(train)- max(train) * 0.1
g = pd.DataFrame()
g['Horizon'] = np.arange(1,len(fc_series)+1, 1)
g['Value'] = pd.Series(fc_series.reset_index(drop = True))
g = g.round(2)
plt.text(x_cor,y_cor, 'FORECAST :- \n %s ' %g.to_string(index = False), bbox = dict(facecolor = 'grey', alpha = 0.1))

for x,y in zip(x,df_macro[col_name]):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 ha='center') # horizontal alignment can be left, right or center
    
tst_x = np.arange(16,21, 1)
for x,y in zip(tst_x,fc_series):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 color = 'g',
                 ha='right') # horizontal alignment can be left, right or center
plt.savefig(str(directory +'\\'+ 'Horizon 4 Images'+'\\' +col_name+ '.jpg'))

print('MAPE :: %s' %"{:.3f}".format(MAPE(test, fc_series)), '%')
YOI = fc_series
YOI

MAPE :: 16.149 %

16    1.040820e+06
17    1.096001e+06
18    1.148326e+06
19    1.086745e+06
dtype: float64

Forecasts of P&L factors

col_name = 'Interest on Advances'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]


p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(0, 0, 1) MAPE=3.374
18    40603.066324
19    40246.129860
20    40246.129860
21    40246.129860
Name: mean, dtype: float64

col_name = 'Int on Resources Operations'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(2, 1, 1) MAPE=1.871
18    21344.874573
19    21602.815647
20    21817.028132
21    21946.523228
Name: mean, dtype: float64

col_name = 'Others Sundry Interest income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.6)
fc_series = exp_model.forecast(4)


Chart_plot(col_name,fc_series)

18    2248.793281
19    3250.146034
20    2824.818797
21    2396.772476
dtype: float64

col_name = 'Total Interest Income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(1, 0, 0) MAPE=2.038
18    65966.549724
19    65222.717865
20    64569.912162
21    63996.993324
Name: mean, dtype: float64

col_name = 'Interest on Deposits'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]


p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(2, 0, 1) MAPE=0.634
18    34919.849754
19    34238.903786
20    33894.161507
21    33875.417152
Name: mean, dtype: float64

col_name = 'Interest on Borrowings'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.75)
fc_series = exp_model.forecast(4)


'''
p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])
'''
Chart_plot(col_name,fc_series)

18    1832.502013
19    1882.915516
20    1743.540464
21    1542.440475
dtype: float64

col_name = 'Others Sundry Interest paid'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=2)
exp_model = exp.fit(smoothing_level=0.4)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    1334.303398
19    1417.019617
20    1378.810568
21    1461.526786
dtype: float64

col_name = 'Total Interest Expenses'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]


p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(1, 0, 1) MAPE=0.422
18    38110.113787
19    38117.094246
20    38122.623949
21    38127.004407
Name: mean, dtype: float64

col_name = 'Net Interest Income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.01)
fc_series = exp_model.forecast(4)


Chart_plot(col_name,fc_series)

18    30283.750195
19    26912.292217
20    29928.392989
21    30970.824798
dtype: float64

col_name = 'Commission, Exc. Brokerage'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.01)
fc_series = exp_model.forecast(4)


Chart_plot(col_name,fc_series)

18    5236.178794
19    8342.994089
20    5056.300598
21    5431.493874
dtype: float64

col_name = 'Profit on Sale_or_Rev of Investments'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.001)
fc_series = exp_model.forecast(4)

# Holt Winters
#fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)
p_values = [0, 1]
d_values = range(0,2)
q_values = range(0, 3)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(0, 1, 0) MAPE=806.054
18    1084.3071
19    1084.3071
20    1084.3071
21    1084.3071
Name: mean, dtype: float64

col_name = 'Forex Income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)

# Holt Winters
#fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    530.474966
19    560.081060
20    443.759850
21    539.140567
dtype: float64

col_name = 'Dividend'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)

# Holt Winters
#fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18     12.887661
19    397.031462
20    103.563244
21    147.721481
dtype: float64

col_name = 'Sundry Other Income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)


# Holt Winters
#fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)


Chart_plot(col_name,fc_series)

Best ARIMA(0, 1, 2) MAPE=34.150
18    1927.311216
19    1761.434298
20    1761.434298
21    1761.434298
Name: mean, dtype: float64

col_name = 'Total Non-Interest Income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=2)
exp_model = exp.fit(smoothing_level=0.4)
fc_series = exp_model.forecast(4)


p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])



p_values = [0, 1]
d_values = [0,1]
q_values = [0,1]
P_values = [0, 1]
D_values = [0, 1]
Q_values = [0, 1]
warnings.filterwarnings("ignore")
best_order, best_seasonal_order = evaluate_ssnl_models(df_PL[col_name], p_values, d_values, q_values, P_values, D_values, Q_values,16,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = SARIMAX(train, order=best_order, seasonal_order=best_seasonal_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
#fc_series = pd.Series(output['mean'])

Chart_plot(col_name,fc_series)

Best ARIMA(2, 1, 1) MAPE=19.403
Best ARIMA(1, 0, 0) Seasonal(1, 0, 1, 4) MAPE=15.094
18    9280.201181
19    8931.519258
20    9229.875416
21    9107.731882
Name: mean, dtype: float64

col_name = 'Total Operating Income'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods=4)
exp_model = exp.fit(smoothing_level=0.6)
fc_series = exp_model.forecast(4)

# Holt Winters
#fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.1,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    36337.176389
19    40440.648378
20    35729.573547
21    39424.490385
dtype: float64

col_name = 'Payment to Employees'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul')
exp_model = exp.fit(smoothing_level=0.3)
#fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    6930.351829
19    6994.644480
20    7059.533572
21    7125.024638
dtype: float64

col_name = 'Provisions for Employees'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
'''
# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)
'''
p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])

Chart_plot(col_name,fc_series)

Best ARIMA(0, 0, 1) MAPE=22.225
18    4696.809503
19    3267.124325
20    3267.124325
21    3267.124325
Name: mean, dtype: float64

col_name = 'Total Staff Expenses'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='ADD', seasonal='MUL', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.15)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    13246.473589
19    13244.028667
20    12983.372284
21    13360.883462
dtype: float64

col_name = 'Rent_Taxes_Lighting'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='ADD', seasonal='MUL', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    1271.538166
19    1422.220660
20    1054.927258
21    1318.523333
dtype: float64

col_name = 'Depreciation on Property'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add')
exp_model = exp.fit(smoothing_level=0.9)
#fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.5, smoothing_trend=0.4,optimized=False)
fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    840.864488
19    842.092385
20    843.322076
21    844.553563
dtype: float64

col_name = 'Others'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.85)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    5668.866271
19    6550.163173
20    5508.957545
21    6054.085023
dtype: float64

col_name = 'Total Overhead Expenses'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=1.1)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    7755.170058
19    9090.813032
20    7900.307930
21    8374.559465
dtype: float64

col_name = 'Total Operating Expenses'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    20835.375281
19    22313.671774
20    20856.445632
21    21570.395714
dtype: float64

col_name = 'Operating Profit'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.9)
#fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    17148.541704
19    17687.227298
20    18242.834574
21    18815.895090
dtype: float64

col_name = 'Income Tax Provisions'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

warnings.filterwarnings("ignore")

model = ARIMA(train, order=(1,2,0))
model_fit = model.fit()
result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])

Chart_plot(col_name,fc_series)

18    1955.180621
19    2248.125390
20    2488.252396
21    2754.933383
Name: mean, dtype: float64

col_name = 'Loan Loss Provisions'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='mul', seasonal='mul', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=1)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)

Chart_plot(col_name,fc_series)

18    5728.195551
19    8445.464540
20    5199.423580
21    5423.306365
dtype: float64

col_name = 'Investment Depreciation'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])

Chart_plot(col_name,fc_series)

Best ARIMA(2, 0, 0) MAPE=50.575
18    506.826678
19    583.776873
20    647.288558
21    654.180752
Name: mean, dtype: float64

col_name = 'Standard Assets Provision'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(2, 1, 0) MAPE=59.285
18    1069.584611
19    -133.666594
20     568.692379
21     357.912089
Name: mean, dtype: float64

col_name = 'Other Provisions'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


Chart_plot(col_name,fc_series)

Best ARIMA(0, 0, 1) MAPE=79.917
18   -1811.360885
19     314.693371
20     314.693371
21     314.693371
Name: mean, dtype: float64

col_name = 'Total Provisions'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

'''
# Exponential Smoothing
exp = ExponentialSmoothing(train, trend='add', seasonal='add', seasonal_periods = 4)
exp_model = exp.fit(smoothing_level=0.3)
fc_series = exp_model.forecast(4)

# Holt Winters
fit2 = Holt(train, exponential=True, initialization_method="estimated").fit(smoothing_level=0.1, smoothing_trend=0.4,optimized=False)
#fc_series = fit2.forecast(4)
'''

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])

Chart_plot(col_name,fc_series)

Best ARIMA(0, 1, 0) MAPE=77.197
18    11885.607416
19    11885.607416
20    11885.607416
21    11885.607416
Name: mean, dtype: float64

col_name = 'Net Profit'
tr_cnt = 18
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]

p_values = [0, 1, 2]
d_values = range(0,2)
q_values = range(0, 2)
warnings.filterwarnings("ignore")
best_order = evaluate_models(df_PL[col_name], p_values, d_values, q_values,tr_cnt,4)
train = df_PL[col_name][:tr_cnt]
test = df_PL[col_name][tr_cnt:]
# define model
model = ARIMA(train, order=best_order)
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])

Chart_plot(col_name,fc_series)

Best ARIMA(0, 1, 0) MAPE=27.689
18    4574.152483
19    4574.152483
20    4574.152483
21    4574.152483
Name: mean, dtype: float64

#Ad-fuller_test

#define function for ADF test
from statsmodels.tsa.stattools import adfuller
def adf_test(timeseries):
    #Perform Dickey-Fuller test:
    print ('Results of Dickey-Fuller Test:')
    dftest = adfuller(timeseries)
    dfoutput = pd.Series(dftest[0:4], index=['Test Statistic','p-value','#Lags Used','Number of Observations Used'])
    for key,value in dftest[4].items():
       dfoutput['Critical Value (%s)'%key] = value
    print (dfoutput)

adf_test(df_PL['Sundry Other Income'])
adf_test(df_PL['Profit on Sale_or_Rev of Investments'])

#Sundry Other Income

Results of Dickey-Fuller Test:
Test Statistic                 -1.800995
p-value                         0.379968
#Lags Used                      9.000000
Number of Observations Used    12.000000
Critical Value (1%)            -4.137829
Critical Value (5%)            -3.154972
Critical Value (10%)           -2.714477
dtype: float64
Results of Dickey-Fuller Test:
Test Statistic                 -4.092192
p-value                         0.000998
#Lags Used                      0.000000
Number of Observations Used    21.000000
Critical Value (1%)            -3.788386
Critical Value (5%)            -3.013098
Critical Value (10%)           -2.646397
dtype: float64

col_name = 'Other Provisions'
tr_cnt = 18
train = df_PL[col_name][13:tr_cnt]
print(train)
test = df_PL[col_name][tr_cnt:]
print(test)


# define model
model = ARIMA(train, order=(0,0,1))
model_fit = model.fit()

result = model_fit.get_forecast(steps=4)
output = result.summary_frame(alpha = 0.05)
fc_series = pd.Series(output['mean'])


plt.figure(figsize=(12,8), dpi=200)
plt.plot(train, label='training',marker = 'o', linestyle= '--')
plt.plot(test, label='actual',marker = 'o', linestyle= '--', color = 'cornflowerblue')
plt.plot(fc_series, label='forecast',marker = 'o', linestyle= ':' , color = 'orange')

mon = ['Jun', "Sep", 'Dec', 'Mar']
yrs = list(np.arange(17,23,1))
x_tks = ['FY' + str(j) +'_'+i.strip() for j in yrs for i in mon]

x = np.arange(0, len(train) + len(fc_series), 1)
plt.xticks(x, x_tks, rotation=75)
plt.legend(loc='upper left', fontsize=8)
plt.grid(True)
'''
for x,y in zip(x,df_PL[col_name]):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 color = 'b',
                 ha='center') # horizontal alignment can be left, right or center

tst_x = np.arange(18,23, 1)
for x,y in zip(tst_x,fc_series):
    label = "{:.2f}".format(y)
    plt.annotate(label, # this is the text
                 (x,y), # these are the coordinates to position the label
                 textcoords="offset points", # how to position the text
                 xytext=(0,10), # distance from text to points (x,y)
                 color = 'darkorange',
                 ha='right') # horizontal alignment can be left, right or center
'''
print(fc_series)
mape = "{:.3f}".format(MAPE(test, fc_series))
#print('MAPE :: %s' %mape, '%')
plt.title(col_name +'- Forecating'+'----MAPE--'+str(mape)+'%')

13    2720.713695
14   -2474.693728
15     338.894400
16    1567.563807
17    4103.495495
Name: Other Provisions, dtype: float64
18    6028.427680
19   -1735.082102
20    2927.819296
21   -2363.397645
Name: Other Provisions, dtype: float64
18   -1264.583170
19     856.583006
20     856.583006
21     856.583006
Name: mean, dtype: float64

Text(0.5, 1.0, 'Other Provisions- Forecating----MAPE--119.333%')